先前我們在 DAY 06 說明了兩種基本的 materializations: view 和 table。
今天要來介紹另一個 materialization: incremental。
又是一件非常複雜的事情，但除非專案規模很小，不然大多數都會需要用到，中度以上的使用者都需要了解。

View 和 Table 兩種 Materializations 的考量

View 和 Table 兩種 materializations 各有不同特性：

• View
  • 不佔儲存空間，create view 不需要等待，且不耗費運算資源。
  • 隨時查到的都是最新資料。
  • 如果邏輯複雜，例如 Join 大量 table 的話，使用（查詢）這個 view 時，select 的當下才會進行運算，可能需要等待。
• Table
  • 佔儲存空間，create table 需要等待並耗費運算資源
  • 查到的資料是最後一次更新的，可能有時間差。
  • table 是實體儲存的資料，與 view 相比，取用時較不需要進行運算，等待時間較短。

在 Teamson 我們使用 dbt 建議的三層架構，在 staging 和 intermediate 通常會使用 view，而在 marts 會開始考慮把一些 model 跑成 table。兩個主要理由：

• 縮短更新時間：有些列數較多的資料源，我們在 marts 會做很多步驟的運算，如果都用 view 的話，更新會跑到天荒地老。這時我們會希望在 marts 的第一步就跑成 table，後續的步驟會快很多。
• 使用者體驗：這些最下游的 model 可能是給終端使用者在 BI 使用，我們希望在 scheduled jobs 就預先把這些 model 跑成 table，這樣在取用的時候才不需要等待太久。

問題來了：把這些資料全都跑成 table 要花很多時間，例如每個小時的定期更新，跑超過 30 分鐘才完成，有什麼辦法改善呢？

這就是我們接下來要介紹的： Incremental Materialization

如何定義 Incremental Model

Incremental refresh 就是每次更新的時候，如果不希望整張表都更新，可以只更新差份。

接下來我要舉一個虛構的例子說明如何定義一個 incremental model。

我們的訂單的資料流如下：
stg_sales_orders -> int_sales_orders -> sales_orders

其中 stg_sales_orders 和 int_sales_orders 我們跑成 view，而 sales_orders 要跑成 table。
排程為每小時一次，資料量為百萬以上。
用欄位 updated_at 來判斷哪些資料有異動（包含新增）。

為了節省時間以及運算資源，我們要用 incremental materialization，只更新過去一個小時新增或異動的資料，而非跑整張表，一般的 table materialization。

config -> materialized = incremental

和 view/table 類似，我們要在 sales_orders.sql 加上 config -> materialized。

{{ config(materialized='incremental') }}

select * from {{ ref('int_sales_orders') }}

用 updated_at 篩選出需要更新的資料

除此之外，我們還要定義哪些資料要更新，我們希望只更新上次更新過後，有異動的資料（包含新增）。

{{ config(materialized='incremental') }}

select * from {{ ref('int_sales_orders') }}

{% if is_incremental() %}

    where updated_at >= (select max(updated_at) from {{ this }})

{% endif %}

上面這段語法中，我們加上 if is_incremental() 的判斷，如果要跑 incremental 時，我們只篩選出 updated_at >= (select max(updated_at) from {{ this }}) 的資料。

{{this}} 就是目標 table 本身，先抓上一次更新時，最新一筆的更新日期到什麼時候。
假設上一次更新時，資料到 updated_at = 10/16 08:30:15，那我們就更新 updated_at >= 10/16 08:30:15 的資料。

請注意這裡我們下的是 >=，想像 10/16 08:30:15 不只有一筆訂單，部份包含在前一次更新，部份不包含。如果下 > 的話，就會有資料漏掉。

用 unique_key 排除重複

那麼，下了 >= 不就會有資料重複的問題嗎？

對的，所以我們需要再加上 unique_key 的參數。這樣就會依 order_id 判斷，資料不重複 insert。

{{ config(materialized='incremental', unique_key='order_id') }}

select * from {{ ref('int_sales_orders') }}

{% if is_incremental() %}

    where updated_at >= (select max(updated_at) from {{ this }})

{% endif %}

這樣就完成基本的 incremental materialization 了。

is_incremental() 為 true 才會使用該篩選條件

並不是加上了 materialized='incremental' 每一次更新的時候就只挑最近一小時的資料，這麼簡單。
上述的語法，會用 is_incremental() 判斷，true 的時候才會用到 updated_at 的條件。

考慮以下情境

• 第一次跑該 model，不存在該目標 table，需要建立整個表。
• 如果跑 dbt run 或 dbt build 時，加上 --full-refresh 時，就需要強制更新全表。

因此，會觸發 is_incremental() = true，需要同時滿足以下三個條件

• 該 model 為 incremental
• 執行指令時沒有放 --full-refresh
• 目標資料庫，已存在該 table

欄位變動該如何處理 -- on_schema_change

因為 incremental 不把整個 table 重建，而是每次更新的時候，把資料一次一次往上疊。如果 model 定義改變，欄位與先前不同，要如何處理呢？這時候會用到的是 on_schema_change 參數。

有以下幾種模式：

• ignore: 系統預設，忽略新的欄位，原有的欄位照常更新。
• fail: 只要偵測到欄位變化，就會更新失敗，跳錯誤訊息。
• append_new_columns: 加上新的欄位，但舊有的欄位即使不存在了，也不會被移除。
• sync_all_columns: 加入新欄位並移除不存在的欄位，但要注意如果是欄位型態異動，可能會出錯。

如果沒有指定的話，預設是 ignore。如果需要調整的話，就加入 config。
舉例，把 on_schema_change 設為 fail：

{{ config(materialized='incremental', unique_key='order_id', on_schema_change='fail') }}

select * from {{ ref('int_sales_orders') }}

{% if is_incremental() %}

    where updated_at >= (select max(updated_at) from {{ this }})

{% endif %}

如果不想每個 model 都設一次，同樣可以從 dbt_project.yml 一次加。

我的習慣是，在正式環境上版時，一定會跑 full refresh，所以不管選哪一個都沒有差。
但在開發時，如果沒有搞懂 on_schema_change 如何運作，就會無限鬼打牆。
只要有用到 incremental，建議一定要了解 on_schema_change。

Incremental 的更新效率

剛開始用 Incremental 的時候，incremental 跟 full refresh，時間完全一樣，一點都沒有變快。
我很確定我用錯了！後來花很多力氣，才有明顯改善。我做了這些調整：

修正篩選 update time 的邏輯

我了解資料庫效能優化需要善用 index，但是我一開始用 SAP 原始資料的更新時間，日期和時間是拆開的兩個欄位。我先把兩個欄位合在一起變成 update_timestamp 再進行判斷。改了好幾次都還是超慢。

我們的資料是用 Stitch 從來源 replicate 到我們的 PostgreSQL 報表資料庫，所以後來我發現，可以用 Stitch 為了資料複寫所加上去的欄位。

找到了單一欄位可以使用，事情就變得簡單很多。

來源 table 設定 index

找到了單一欄位可以使用，我就能在資料源，用於判斷 update_at 的欄位，設定 index。

目標 table 設定 index

最後，由 dbt 產出的目標 table 我也有設定 index，希望判斷 unique key 的時候不會花太久時間。

數個 incremental model 的下游 incremental model

如果有兩個 incremental model: table_1, table_2，下游有個 table_3 同時用到那兩個 table，也希望設計為 incremental model，該怎麼做呢？

我的方法是，table_3 同樣定義一個欄位 updated_at 為 greatest(table_1.updated_at, table.updated_at)，is_incremental 的判斷就放

{% if is_incremental() %}

    where
        greatest(table_1.updated_at, table_2.updated_at)
        >= (select max(updated_at) from {{ this }})

{% endif %}

其他考量：刪除的資料列？

如果來源的資料，有資料被刪除的話，也需要納入考量。
我沒看過相關的討論，如果有的話，歡迎留言給我。

特別推薦：Jolanda Zwagemaker 的分享

dbt: The key to Scaling Your Data Team with Grace
https://www.youtube.com/watch?v=iBNSEbKnFBI&t=7275s （大約為 2:00:00 - 2:30:00）

這是 Jolanda 先前在 dbt Taipei Meetup 分享的錄影，是我印象特別深刻的一場。
全程英文，但她用很生動易懂的例子，說明了她在 Wise 使用及優化 incremental strategy 的心路歷程。
當時我也深受更新時間所苦，聽了特別有感觸。

今日小結＆明日預告

依據 dbt 官方的建議

• 🔍 Start with a view. When the view gets too long to query for end users,
• ⚒️ Make it a table. When the table gets too long to build in your dbt Jobs,
• 📚 Build it incrementally. That is, layer the data on in chunks as it comes in.

先從 view 開始，如果太慢的話，改用 table。
如果 table 跑的時間太久，再用 incremental。

雖然 incremental 很重要，但是學問也很深。
如果還沒有實際開始導入的朋友們，讀到這邊可能會覺得很難理解，建議可以照順序一步一步導入之後，等需要用到 incremental 的時候再來細讀。

明天的主題：Exposures, Tags, Targets

參考資料

• Incremental models https://docs.getdbt.com/docs/build/incremental-models
• Incremental models in-depth https://docs.getdbt.com/guides/best-practices/materializations/4-incremental-models
• Materializations best practices https://docs.getdbt.com/guides/best-practices/materializations/1-guide-overview
• Best practices for materializations https://docs.getdbt.com/guides/best-practices/materializations/5-best-practices
• 如何使用 dbt incremental 增量更新你的資料? https://ithelp.ithome.com.tw/articles/10323892

歡迎加入 dbt community
對 dbt 或 data 有興趣 👋？歡迎加入 dbt community 到 #local-taipei 找我們，也有實體 Meetup 請到 dbt Taipei Meetup 報名參加